package sfeir.ergosum.dao;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.List;

import sfeir.ergosum.model.Address;
import sfeir.ergosum.model.Contact;
import sfeir.ergosum.model.Email;
import sfeir.ergosum.model.Group;
import sfeir.ergosum.model.LetterIndex;
import sfeir.ergosum.model.NetworkItem;
import sfeir.ergosum.model.Person;
import sfeir.ergosum.model.Tel;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteCursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

public class DatabaseProvider {

    protected static final String DATABASE_NAME = "ergosum.db";
    protected static final String DATABASE_TABLE_GROUP = "groupt";
    protected static final String DATABASE_TABLE_CONTACT = "contact";
    protected static final String DATABASE_TABLE_PERSON = "person";
    protected static final String DATABASE_TABLE_ADDRESS = "address";
    protected static final String DATABASE_TABLE_TEL = "phone";
    protected static final String DATABASE_TABLE_EMAIL = "email";
    protected static final String DATABASE_TABLE_NETWORK = "network";
    protected static final String DATABASE_TABLE_LETTERINDEX = "letterindex";
    protected static final String DATABASE_TABLE_PHONEINDEX = "phoneindex";
    protected static final String DATABASE_TABLE_MAILINDEX = "mailindex";
    protected static final int DATABASE_VERSION = 30;

    protected static final String alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

    // The index (key) column name for use in where clauses.
    public static final String KEY_ID = "_id";
    public static final int KEY_ID_COLUMN = 0;
    // The name and column index of each column in your database.

    public static final String KEY_GROUP_ORDER = "position";
    public static final int GROUP_ORDER_COLUMN = 1;

    public static final String KEY_GROUP_LABEL = "label";
    public static final int GROUP_LABEL_COLUMN = 2;

    public static final String KEY_CONTACT_GROUP = "group_id";
    public static final int CONTACT_GROUP_COLUMN = 1;

    public static final String KEY_CONTACT_PERSON = "person_id";
    public static final int CONTACT_PERSON_COLUMN = 2;

    public static final String KEY_CONTACT_URL = "url";
    public static final int CONTACT_URL_COLUMN = 3;

    public static final String KEY_CONTACT_ALIAS = "alias";
    public static final int CONTACT_ALIAS_COLUMN = 4;

    public static final String KEY_CONTACT_UPDATE = "updated";
    public static final int CONTACT_UPDATE_COLUMN = 5;

    public static final String KEY_CONTACT_PHONEID = "phoneid";
    public static final int CONTACT_PHONEID_COLUMN = 6;

    public static final String KEY_CONTACT_LETTERID = "letterid";
    public static final int CONTACT_LETTERID_COLUMN = 7;

    public static final String KEY_CONTACT_PHONEINDEX = "phoneindex";
    public static final int CONTACT_PHONEINDEX_COLUMN = 8;

    public static final String KEY_CONTACT_MAILINDEX = "mailindex";
    public static final int CONTACT_MAILINDEX_COLUMN = 9;

    public static final String KEY_PERSON_NAME = "name";
    public static final int PERSON_NAME_COLUMN = 1;

    public static final String KEY_PERSON_FIRSTNAME = "firstname";
    public static final int PERSON_FIRSTNAME_COLUMN = 2;

    public static final String KEY_PERSON_NICKNAME = "nickname";
    public static final int PERSON_NICKNAME_COLUMN = 3;

    public static final String KEY_PERSON_BIRTHDAY = "birthday";
    public static final int PERSON_BIRTHDAY_COLUMN = 4;

    public static final String KEY_PERSON_TITLE = "title";
    public static final int PERSON_TITLE_COLUMN = 5;

    public static final String KEY_PERSON_ABOUTME = "aboutme";
    public static final int PERSON_ABOUTME_COLUMN = 6;

    public static final String KEY_PERSON_COMPANY = "company";
    public static final int PERSON_COMPANY_COLUMN = 7;

    public static final String KEY_PERSON_URL = "url";
    public static final int PERSON_URL_COLUMN = 8;

    public static final String KEY_PERSON_IMAGEURL = "imageurl";
    public static final int PERSON_IMAGEURL_COLUMN = 9;

    public static final String KEY_ADDRESS_PERSON = "person_id";
    public static final int ADDRESS_PERSON_COLUMN = 1;

    public static final String KEY_ADDRESS_TYPE = "type";
    public static final int ADDRESS_TYPE_COLUMN = 2;

    public static final String KEY_ADDRESS_VALUE = "value";
    public static final int ADDRESS_VALUE_COLUMN = 3;

    public static final String KEY_TEL_PERSON = "person_id";
    public static final int TEL_PERSON_COLUMN = 1;

    public static final String KEY_TEL_TYPE = "type";
    public static final int TEL_TYPE_COLUMN = 2;

    public static final String KEY_TEL_VALUE = "value";
    public static final int TEL_VALUE_COLUMN = 3;

    public static final String KEY_EMAIL_PERSON = "person_id";
    public static final int EMAIL_PERSON_COLUMN = 1;

    public static final String KEY_EMAIL_TYPE = "type";
    public static final int EMAIL_TYPE_COLUMN = 2;

    public static final String KEY_EMAIL_VALUE = "value";
    public static final int EMAIL_VALUE_COLUMN = 3;

    public static final String KEY_NETWORK_PERSON = "person_id";
    public static final int NETWORK_PERSON_COLUMN = 1;

    public static final String KEY_NETWORK_TYPE = "type";
    public static final int NETWORK_TYPE_COLUMN = 2;

    public static final String KEY_NETWORK_VALUE = "value";
    public static final int NETWORK_VALUE_COLUMN = 3;

    public static final String KEY_LETTERINDEX_LETTER = "letter";
    public static final int LETTERINDEX_LETTER_COLUMN = 1;

    public static final String KEY_LETTERINDEX_OCCU = "occu";
    public static final int LETTERINDEX_OCCU_COLUMN = 2;

    public static final String KEY_PHONEINDEX_LETTER = "letter";
    public static final int PHONEINDEX_LETTER_COLUMN = 1;

    public static final String KEY_PHONEINDEX_OCCU = "occu";
    public static final int PHONEINDEX_OCCU_COLUMN = 2;

    public static final String KEY_MAILINDEX_LETTER = "letter";
    public static final int MAILINDEX_LETTER_COLUMN = 1;

    public static final String KEY_MAILINDEX_OCCU = "occu";
    public static final int MAILINDEX_OCCU_COLUMN = 2;

    // SQL Statement to create a new database.

    protected static final String DATABASE_CREATE_GROUP = "create table "
            + DATABASE_TABLE_GROUP + " (" + KEY_ID
            + " integer primary key autoincrement, " + KEY_GROUP_LABEL
            + " text not null, " + KEY_GROUP_ORDER + " text not null " + ");";

    protected static final String DATABASE_CREATE_CONTACT = "create table "
            + DATABASE_TABLE_CONTACT + " (" + KEY_ID
            + " integer primary key autoincrement, " + KEY_CONTACT_GROUP
            + " text not null, " + KEY_CONTACT_PERSON + " text not null, "
            + KEY_CONTACT_URL + " text not null, " + KEY_CONTACT_ALIAS
            + " text, " + KEY_CONTACT_UPDATE + " integer, "
            + KEY_CONTACT_PHONEID + " integer, " + KEY_CONTACT_LETTERID
            + " integer, " + KEY_CONTACT_PHONEINDEX + " integer, "
            + KEY_CONTACT_MAILINDEX + " integer " + ");";

    protected static final String DATABASE_CREATE_CONTACT_INDEX = "CREATE INDEX "
            + DATABASE_TABLE_CONTACT
            + "index ON "
            + DATABASE_TABLE_CONTACT
            + "(" + KEY_CONTACT_GROUP + ");";

    protected static final String DATABASE_CREATE_CONTACT_INDEX2 = "CREATE INDEX "
            + DATABASE_TABLE_CONTACT
            + "index2 ON "
            + DATABASE_TABLE_CONTACT
            + "(" + KEY_ID + ");";

    protected static final String DATABASE_CREATE_PERSON = "create table "
            + DATABASE_TABLE_PERSON + " (" + KEY_ID
            + " integer primary key autoincrement, " + KEY_PERSON_NAME
            + " text not null, " + KEY_PERSON_FIRSTNAME + " text, "
            + KEY_PERSON_NICKNAME + " text, " + KEY_PERSON_BIRTHDAY + " text, "
            + KEY_PERSON_TITLE + " text, " + KEY_PERSON_ABOUTME + " text, "
            + KEY_PERSON_COMPANY + " text, " + KEY_PERSON_URL + " text, "
            + KEY_PERSON_IMAGEURL + " text " + ");";

    protected static final String DATABASE_CREATE_ADDRESS = "create table "
            + DATABASE_TABLE_ADDRESS + " (" + KEY_ID
            + " integer primary key autoincrement, " + KEY_ADDRESS_PERSON
            + " text not null, " + KEY_ADDRESS_TYPE + " text not null, "
            + KEY_ADDRESS_VALUE + " text not null " + ");";

    protected static final String DATABASE_CREATE_TEL = "create table "
            + DATABASE_TABLE_TEL + " (" + KEY_ID
            + " integer primary key autoincrement, " + KEY_TEL_PERSON
            + " text not null, " + KEY_TEL_TYPE + " text not null, "
            + KEY_TEL_VALUE + " text not null " + ");";

    protected static final String DATABASE_CREATE_EMAIL = "create table "
            + DATABASE_TABLE_EMAIL + " (" + KEY_ID
            + " integer primary key autoincrement, " + KEY_EMAIL_PERSON
            + " text not null, " + KEY_EMAIL_TYPE + " text not null, "
            + KEY_EMAIL_VALUE + " text not null " + ");";

    protected static final String DATABASE_CREATE_NETWORK = "create table "
            + DATABASE_TABLE_NETWORK + " (" + KEY_ID
            + " integer primary key autoincrement, " + KEY_NETWORK_PERSON
            + " text not null, " + KEY_NETWORK_TYPE + " text not null, "
            + KEY_NETWORK_VALUE + " text not null " + ");";

    protected static final String DATABASE_CREATE_LETTERINDEX = "create table "
            + DATABASE_TABLE_LETTERINDEX + " (" + KEY_ID
            + " integer primary key autoincrement, " + KEY_LETTERINDEX_LETTER
            + " text not null, " + KEY_LETTERINDEX_OCCU + " integer );";

    protected static final String DATABASE_CREATE_PHONEINDEX = "create table "
            + DATABASE_TABLE_PHONEINDEX + " (" + KEY_ID
            + " integer primary key autoincrement, " + KEY_PHONEINDEX_LETTER
            + " text not null, " + KEY_PHONEINDEX_OCCU + " integer );";

    protected static final String DATABASE_CREATE_MAILINDEX = "create table "
            + DATABASE_TABLE_MAILINDEX + " (" + KEY_ID
            + " integer primary key autoincrement, " + KEY_MAILINDEX_LETTER
            + " text not null, " + KEY_MAILINDEX_OCCU + " integer );";

    // Variable to hold the database instance
    private SQLiteDatabase db;

    // Database open/upgrade helper
    private SQLiteOpenHelper dbConnection;

    public DatabaseProvider(SQLiteOpenHelper dbConnection) {
        this.dbConnection = dbConnection;
    }

    public DatabaseProvider open() throws SQLException {
        try {
            db = dbConnection.getWritableDatabase();
        } catch (SQLiteException ex) {
            db = dbConnection.getReadableDatabase();
        }
        return this;
    }

    public void close() {
        db.close();
    }

    /**
     * Inserts a new group inside the database
     * 
     * @param group
     *            The group to be inserted
     * @return The id of group in database
     */
    public long insertGroup(Group group) {
        ContentValues groupValues = new ContentValues();
        groupValues.put(KEY_GROUP_ORDER, group.getOrder());
        groupValues.put(KEY_GROUP_LABEL, group.getName());
        return db.insert(DATABASE_TABLE_GROUP, null, groupValues);
    }

    /**
     * Updates a group in database
     * 
     * @param group
     *            The group to update
     * @return The group id
     */
    public long updateGroup(Group group) {

        String whereGroup = KEY_ID + "=" + group.getId();

        ContentValues groupValues = new ContentValues();
        groupValues.put(KEY_GROUP_ORDER, group.getOrder());
        groupValues.put(KEY_GROUP_LABEL, group.getName());
        db.update(DATABASE_TABLE_GROUP, groupValues, whereGroup, null);
        return group.getId();
    }

    /**
     * Returns all the groups in database + a default group called "Contacts"
     * with id -1
     * 
     * @return A list of groups
     */
    public List<Group> getAllGroups() {
        List<Group> groups = new ArrayList<Group>();
        Cursor allGroups = db.query(DATABASE_TABLE_GROUP, new String[] {
                KEY_ID, KEY_GROUP_ORDER, KEY_GROUP_LABEL }, null, null, null,
                null, null);

        if (allGroups.moveToFirst()) {
            do {
                Group g = new Group(allGroups.getInt(KEY_ID_COLUMN), allGroups
                        .getInt(GROUP_ORDER_COLUMN), allGroups
                        .getString(GROUP_LABEL_COLUMN));
                groups.add(g);
            } while (allGroups.moveToNext());
        }

        allGroups.close();

        Collections.sort(groups, new Comparator<Group>() {
            @Override
            public int compare(Group object1, Group object2) {
                return Float.compare(object1.getOrder(), object2.getOrder());
            }
        });

        Group defaultgroup = new Group(-1, -1, "Contacts");
        groups.add(defaultgroup);
        return groups;
    }

    /**
     * Deletes a group from database
     * 
     * @param groupId
     *            The id of the group to delete
     * @return true if something has been deleted, else false
     */
    public boolean removeGroup(long groupId) {

        String whereContacts = KEY_CONTACT_GROUP + "=" + groupId;
        Cursor validContacts = db.query(DATABASE_TABLE_CONTACT,
                new String[] { KEY_ID }, whereContacts, null, null, null, null);

        if (validContacts.moveToFirst()) {

            do {
                String whereContact = KEY_ID + "="
                        + validContacts.getInt(KEY_ID_COLUMN);
                ContentValues cv = new ContentValues();
                cv.put(KEY_CONTACT_GROUP, -1);
                db.update(DATABASE_TABLE_CONTACT, cv, whereContact, null);
            } while (validContacts.moveToNext());

        }

        validContacts.close();

        return db.delete(DATABASE_TABLE_GROUP, KEY_ID + "=" + groupId, null) > 0;
    }

    /**
     * Gets all the contacts in a specific group
     * 
     * @param groupId
     *            The group to get all the contacts for
     * @return An ArrayList of contacts
     */
    public List<Contact> getAllContactsFromGroup(long groupId) {
        List<Contact> groupContacts = new ArrayList<Contact>();

        String whereContacts = KEY_CONTACT_GROUP + "=" + groupId;
        Cursor validContacts = db.query(DATABASE_TABLE_CONTACT,
                new String[] { KEY_ID }, whereContacts, null, null, null, null);

        if (validContacts.moveToFirst()) {

            do {
                groupContacts.add(getFullContact(validContacts
                        .getInt(KEY_ID_COLUMN)));
            } while (validContacts.moveToNext());

        }

        validContacts.close();

        return sortContacts(groupContacts);
    }

    /**
     * Gets all the light contacts in a specific group
     * 
     * @param groupId
     *            The group to get all the contacts for
     * @return An ArrayList of contacts
     */
    public List<Contact> getAllLightContactsFromGroup(long groupId) {
        if (groupId == -2)
            return getAllLightContacts(false, true);
        if (groupId == -3)
            return getAllLightContacts(true, false);
        List<Contact> groupContacts = new ArrayList<Contact>();

        String whereContacts = KEY_CONTACT_GROUP + "=" + groupId;
        Cursor validContacts = db.query(DATABASE_TABLE_CONTACT, new String[] {
                KEY_ID, KEY_CONTACT_GROUP, KEY_CONTACT_PERSON, KEY_CONTACT_URL,
                KEY_CONTACT_ALIAS, KEY_CONTACT_LETTERID }, whereContacts, null,
                null, null, null);

        if (validContacts.moveToFirst()) {

            do {
                Contact contact = new Contact();
                contact.setId(validContacts.getLong(0));
                contact.setGroupId(validContacts.getInt(1));
                Person person = getLightPerson(validContacts.getLong(2));
                contact.setPerson(person);
                contact.setUrl(validContacts.getString(3));
                contact.setAlias(validContacts.getString(4));
                contact.setLetterId(validContacts.getLong(5));
                groupContacts.add(contact);
            } while (validContacts.moveToNext());

        }
        validContacts.close();

        return sortContacts(groupContacts);
    }

    /**
     * Inserts a contact and its person in the database
     * 
     * @param contact
     *            Contact to be inserted in database
     * @return The id of the inserted contact
     */
    public long insertContact(Contact contact) {
        long startTime = new Date().getTime();

        Person _myPerson = contact.getPerson();

        ContentValues personValues = new ContentValues();
        personValues.put(KEY_PERSON_NAME, _myPerson.getName());
        personValues.put(KEY_PERSON_FIRSTNAME, _myPerson.getFirstname());
        personValues.put(KEY_PERSON_NICKNAME, _myPerson.getNickname());
        personValues.put(KEY_PERSON_ABOUTME, _myPerson.getAboutMe());
        personValues.put(KEY_PERSON_COMPANY, _myPerson.getCompany());
        personValues.put(KEY_PERSON_TITLE, _myPerson.getTitle());
        personValues.put(KEY_PERSON_URL, _myPerson.getUrl());
        personValues.put(KEY_PERSON_BIRTHDAY, _myPerson.getBirthDay());
        personValues.put(KEY_PERSON_IMAGEURL, _myPerson.getPhoto()
                .getImageUrl());

        long personId = db.insert(DATABASE_TABLE_PERSON, null, personValues);

        List<Email> emails = _myPerson.getEmails();
        for (Email e : emails) {
            ContentValues emailValues = new ContentValues();
            emailValues.put(KEY_EMAIL_PERSON, personId);
            emailValues.put(KEY_EMAIL_TYPE, e.getTypeEmail());
            emailValues.put(KEY_EMAIL_VALUE, e.getEmail());
            db.insert(DATABASE_TABLE_EMAIL, null, emailValues);
        }

        List<Tel> tels = _myPerson.getTels();
        for (Tel t : tels) {
            ContentValues telValues = new ContentValues();
            telValues.put(KEY_TEL_PERSON, personId);
            telValues.put(KEY_TEL_TYPE, t.getType());
            telValues.put(KEY_TEL_VALUE, t.getPhoneNumber());
            db.insert(DATABASE_TABLE_TEL, null, telValues);
        }

        List<Address> adresses = _myPerson.getAddresses();
        for (Address a : adresses) {
            ContentValues adrValues = new ContentValues();
            adrValues.put(KEY_ADDRESS_PERSON, personId);
            adrValues.put(KEY_ADDRESS_TYPE, a.getType());
            adrValues.put(KEY_ADDRESS_VALUE, a.getFullValue());
            db.insert(DATABASE_TABLE_ADDRESS, null, adrValues);
        }

        List<NetworkItem> networks = _myPerson.getNetworkItems();
        for (NetworkItem n : networks) {
            ContentValues networkValues = new ContentValues();
            networkValues.put(KEY_NETWORK_PERSON, personId);
            networkValues.put(KEY_NETWORK_TYPE, n.getNetworkItemType());
            networkValues.put(KEY_NETWORK_VALUE, n.getNetworkItemLink());
            db.insert(DATABASE_TABLE_NETWORK, null, networkValues);
        }

        ContentValues contactValues = new ContentValues();
        contactValues.put(KEY_CONTACT_GROUP, -1);
        contactValues.put(KEY_CONTACT_PERSON, personId);
        contactValues.put(KEY_CONTACT_URL, contact.getUrl());
        contactValues.put(KEY_CONTACT_ALIAS, contact.getAlias());
        contactValues.put(KEY_CONTACT_UPDATE, contact.getUpdated());
        contactValues.put(KEY_CONTACT_PHONEID, -1);
        long index = getLetterIndex(contact.getPerson().getName().substring(0,
                1));
        incrementLetterIndex(index);
        contactValues.put(KEY_CONTACT_LETTERID, index);
        if (tels.size() > 0) {
            index = getPhoneIndex(contact.getPerson().getName().substring(0, 1));
            incrementPhoneIndex(index);
            contactValues.put(KEY_CONTACT_PHONEINDEX, index);
        }
        if (emails.size() > 0) {
            index = getMailIndex(contact.getPerson().getName().substring(0, 1));
            incrementMailIndex(index);
            contactValues.put(KEY_CONTACT_MAILINDEX, index);
        }

        long stopTime = new Date().getTime();
        Log.e("insertContact", stopTime - startTime + " ms");
        return db.insert(DATABASE_TABLE_CONTACT, null, contactValues);
    }

    /**
     * Deletes a contact from database, with its person
     * 
     * @param contact
     *            Contact to be deleted
     * @return true if contact has been successfully deleted
     */
    public boolean removeContact(Contact contact) {

        Contact c = getLightContact(contact.getId());
        boolean success = true;
        long personId = c.getPerson().getId();

        db.delete(DATABASE_TABLE_ADDRESS, KEY_ADDRESS_PERSON + "=" + personId,
                null);
        db.delete(DATABASE_TABLE_TEL, KEY_TEL_PERSON + "=" + personId, null);
        db
                .delete(DATABASE_TABLE_EMAIL,
                        KEY_EMAIL_PERSON + "=" + personId, null);
        db.delete(DATABASE_TABLE_NETWORK, KEY_NETWORK_PERSON + "=" + personId,
                null);
        success = db.delete(DATABASE_TABLE_PERSON, KEY_ID + "=" + personId,
                null) > 0
                && success;
        success = db.delete(DATABASE_TABLE_CONTACT, KEY_ID + "="
                + contact.getId(), null) > 0
                && success;
        decrementLetterIndex(c.getLetterId());
        if (c.getPhoneIndex() != 0)
            decrementPhoneIndex(c.getPhoneIndex());
        if (c.getMailIndex() != 0)
            decrementMailIndex(c.getMailIndex());

        return success;
    }

    /**
     * Deletes a contact from database
     * 
     * @param contactId
     *            The id of the group to delete
     * @return true if something has been deleted, else false
     */
    public boolean removeContact(long contactId) {

        Contact c = getLightContact(contactId);
        boolean success = true;
        long personId = c.getPerson().getId();

        db.delete(DATABASE_TABLE_ADDRESS, KEY_ADDRESS_PERSON + "=" + personId,
                null);
        db.delete(DATABASE_TABLE_TEL, KEY_TEL_PERSON + "=" + personId, null);
        db
                .delete(DATABASE_TABLE_EMAIL,
                        KEY_EMAIL_PERSON + "=" + personId, null);
        db.delete(DATABASE_TABLE_NETWORK, KEY_NETWORK_PERSON + "=" + personId,
                null);
        success = db.delete(DATABASE_TABLE_PERSON, KEY_ID + "=" + personId,
                null) > 0
                && success;
        success = db.delete(DATABASE_TABLE_CONTACT, KEY_ID + "=" + contactId,
                null) > 0
                && success;
        decrementLetterIndex(c.getLetterId());
        if (c.getPhoneIndex() != 0)
            decrementPhoneIndex(c.getPhoneIndex());
        if (c.getMailIndex() != 0)
            decrementMailIndex(c.getMailIndex());

        return success;
    }

    /**
     * Gets a full contact (including person) from the database
     * 
     * @param contactId
     *            The wanted contact id
     * @return The contact
     */
    public Contact getFullContact(long contactId) {

        Contact c = new Contact();
        c.setId(contactId);
        String whereContact = KEY_ID + "=" + contactId;
        Cursor contactResult = db.query(DATABASE_TABLE_CONTACT, new String[] {
                KEY_ID, KEY_CONTACT_GROUP, KEY_CONTACT_PERSON, KEY_CONTACT_URL,
                KEY_CONTACT_ALIAS, KEY_CONTACT_UPDATE, KEY_CONTACT_PHONEID,
                KEY_CONTACT_LETTERID, KEY_CONTACT_PHONEINDEX,
                KEY_CONTACT_MAILINDEX }, whereContact, null, null, null, null);

        long _personIndex = -1;

        if (contactResult.getCount() > 0) {

            if (contactResult.moveToFirst()) {

                c.setGroupId(contactResult.getInt(CONTACT_GROUP_COLUMN));
                c.setAlias(contactResult.getString(CONTACT_ALIAS_COLUMN));
                c.setUrl(contactResult.getString(CONTACT_URL_COLUMN));
                c.setUpdated(contactResult.getLong(CONTACT_UPDATE_COLUMN));
                _personIndex = Long.parseLong(contactResult
                        .getString(CONTACT_PERSON_COLUMN));
                c.setPhoneId(contactResult.getLong(CONTACT_PHONEID_COLUMN));
                c.setLetterId(contactResult.getLong(CONTACT_LETTERID_COLUMN));
                c.setPhoneIndex(contactResult
                        .getLong(CONTACT_PHONEINDEX_COLUMN));
                c.setMailIndex(contactResult.getLong(CONTACT_MAILINDEX_COLUMN));
            }

            c.setPerson(getPerson(_personIndex));
            contactResult.close();
            return c;
        }

        else {
            contactResult.close();
            return null;
        }
    }

    /**
     * Gets a contact from an url (referring to the url field of Contact)
     * 
     * @param url
     * @return
     */
    public Contact getContactByURL(String url) {

        String whereContact = KEY_CONTACT_URL + "= \"" + url + "\"";
        Cursor contactResult = db.query(DATABASE_TABLE_CONTACT, new String[] {
                KEY_ID, KEY_CONTACT_GROUP, KEY_CONTACT_PERSON, KEY_CONTACT_URL,
                KEY_CONTACT_ALIAS, KEY_CONTACT_UPDATE, KEY_CONTACT_PHONEID,
                KEY_CONTACT_LETTERID }, whereContact, null, null, null, null);

        long _personIndex = -1;
        Contact c = null;
        if (contactResult.getCount() > 0) {

            c = new Contact();
            if (contactResult.moveToFirst()) {
                c.setId(contactResult.getLong(KEY_ID_COLUMN));
                c.setGroupId(contactResult.getInt(CONTACT_GROUP_COLUMN));
                c.setAlias(contactResult.getString(CONTACT_ALIAS_COLUMN));
                c.setUrl(contactResult.getString(CONTACT_URL_COLUMN));
                c.setUpdated(contactResult.getLong(CONTACT_UPDATE_COLUMN));
                c.setPhoneId(contactResult.getLong(CONTACT_PHONEID_COLUMN));
                _personIndex = Long.parseLong(contactResult
                        .getString(CONTACT_PERSON_COLUMN));
                c.setLetterId(contactResult.getLong(CONTACT_LETTERID_COLUMN));
            }
            c.setPerson(getPerson(_personIndex));
        }
        contactResult.close();
        return c;
    }

    /**
     * Gets a contact from an url (referring to the url field of Contact)
     * 
     * @param url
     * @return
     */
    public long getContactIdByURL(String url) {

        String whereContact = KEY_CONTACT_URL + "= \"" + url + "\"";
        Cursor contactResult = db.query(DATABASE_TABLE_CONTACT,
                new String[] { KEY_ID }, whereContact, null, null, null, null);

        if (contactResult.getCount() > 0) {

            if (contactResult.moveToFirst()) {
                long result = contactResult.getLong(KEY_ID_COLUMN);
                contactResult.close();
                return result;
            }
        }
        return -1;
    }

    /**
     * Gets a person from its id
     * 
     * @param personId
     * @return
     */
    public Person getPerson(long personId) {

        Person p = new Person();
        p.setId(personId);

        String wherePerson = KEY_ID + "=" + personId;
        Cursor personResult = db.query(DATABASE_TABLE_PERSON, new String[] {
                KEY_ID, KEY_PERSON_NAME, KEY_PERSON_FIRSTNAME,
                KEY_PERSON_NICKNAME, KEY_PERSON_BIRTHDAY, KEY_PERSON_TITLE,
                KEY_PERSON_ABOUTME, KEY_PERSON_COMPANY, KEY_PERSON_URL,
                KEY_PERSON_IMAGEURL }, wherePerson, null, null, null, null);

        if (personResult.getCount() > 0) {

            if (personResult.moveToFirst()) {

                p.setName(personResult.getString(PERSON_NAME_COLUMN));
                p.setFirstname(personResult.getString(PERSON_FIRSTNAME_COLUMN));
                p.setNickname(personResult.getString(PERSON_NICKNAME_COLUMN));
                p.setAboutMe(personResult.getString(PERSON_ABOUTME_COLUMN));
                p.setTitle(personResult.getString(PERSON_TITLE_COLUMN));
                p.setCompany(personResult.getString(PERSON_COMPANY_COLUMN));
                p.setUrl(personResult.getString(PERSON_URL_COLUMN));
                p.setBirthDay(personResult.getString(PERSON_BIRTHDAY_COLUMN));
                p.getPhoto().setImageUrl(
                        personResult.getString(PERSON_IMAGEURL_COLUMN));

            }

            personResult.close();

            String whereEmail = KEY_EMAIL_PERSON + " = " + personId;
            SQLiteCursor emailResult = (SQLiteCursor) db.query(
                    DATABASE_TABLE_EMAIL,
                    new String[] { KEY_ID, KEY_EMAIL_PERSON, KEY_EMAIL_TYPE,
                            KEY_EMAIL_VALUE }, whereEmail, null, null, null,
                    null);
            if (emailResult.moveToFirst()) {
                do {
                    Email e = new Email(emailResult
                            .getString(EMAIL_VALUE_COLUMN), Integer
                            .parseInt(emailResult.getString(EMAIL_TYPE_COLUMN)));
                    e.set_id(emailResult.getInt(0));
                    p.getEmails().add(e);
                } while (emailResult.moveToNext());
            }
            emailResult.close();

            String whereTel = KEY_TEL_PERSON + " = " + personId;
            SQLiteCursor telResult = (SQLiteCursor) db.query(
                    DATABASE_TABLE_TEL, new String[] { KEY_ID, KEY_TEL_PERSON,
                            KEY_TEL_TYPE, KEY_TEL_VALUE }, whereTel, null,
                    null, null, null);
            if (telResult.moveToFirst()) {
                do {
                    Tel t = new Tel(telResult.getString(TEL_VALUE_COLUMN),
                            Integer.parseInt(telResult
                                    .getString(TEL_TYPE_COLUMN)));
                    t.set_id(telResult.getInt(0));
                    p.getTels().add(t);
                } while (telResult.moveToNext());
            }
            telResult.close();

            String whereAdr = KEY_ADDRESS_PERSON + " = " + personId;
            SQLiteCursor adrResult = (SQLiteCursor) db.query(
                    DATABASE_TABLE_ADDRESS, new String[] { KEY_ID,
                            KEY_ADDRESS_PERSON, KEY_ADDRESS_TYPE,
                            KEY_ADDRESS_VALUE }, whereAdr, null, null, null,
                    null);
            if (adrResult.moveToFirst()) {
                do {
                    Address a = new Address(adrResult
                            .getString(ADDRESS_VALUE_COLUMN), Integer
                            .parseInt(adrResult.getString(ADDRESS_TYPE_COLUMN)));
                    a.set_id(adrResult.getInt(0));
                    p.getAddresses().add(a);
                } while (adrResult.moveToNext());
            }
            adrResult.close();

            String whereNet = KEY_NETWORK_PERSON + " = " + personId;
            SQLiteCursor netResult = (SQLiteCursor) db.query(
                    DATABASE_TABLE_NETWORK, new String[] { KEY_ID,
                            KEY_NETWORK_PERSON, KEY_NETWORK_TYPE,
                            KEY_NETWORK_VALUE }, whereNet, null, null, null,
                    null);
            if (netResult.moveToFirst()) {
                do {
                    NetworkItem ni = new NetworkItem(netResult
                            .getString(NETWORK_TYPE_COLUMN), netResult
                            .getString(NETWORK_VALUE_COLUMN));
                    ni.set_id(netResult.getInt(0));
                    p.getNetworkItems().add(ni);
                } while (netResult.moveToNext());
            }
            netResult.close();

            return p;
        }

        else
            return null;
    }

    /**
     * Gets a light person from its id
     * 
     * @param personId
     * @return
     */
    public Person getLightPerson(long personId) {

        Person p = new Person();
        p.setId(personId);

        String wherePerson = KEY_ID + "=" + personId;
        Cursor personResult = db.query(DATABASE_TABLE_PERSON, new String[] {
                KEY_PERSON_NAME, KEY_PERSON_FIRSTNAME, KEY_PERSON_IMAGEURL },
                wherePerson, null, null, null, null);

        if (personResult.getCount() > 0) {

            if (personResult.moveToFirst()) {

                p.setName(personResult.getString(0));
                p.setFirstname(personResult.getString(1));
                p.getPhoto().setImageUrl(personResult.getString(2));
            }

            personResult.close();

            return p;
        }

        else
            return null;
    }

    /**
     * Gets a light person from its id
     * 
     * @param personId
     * @return
     */
    public Contact getLightContact(long contactId) {

        Contact c = null;

        String wherePerson = KEY_ID + "=" + contactId;
        Cursor contactResult = db.query(DATABASE_TABLE_CONTACT, new String[] {
                KEY_ID, KEY_CONTACT_GROUP, KEY_CONTACT_PERSON, KEY_CONTACT_URL,
                KEY_CONTACT_ALIAS, KEY_CONTACT_LETTERID }, wherePerson, null,
                null, null, null);

        if (contactResult.getCount() > 0) {

            if (contactResult.moveToFirst()) {

                c = new Contact();
                c.setId(contactResult.getLong(KEY_ID_COLUMN));
                c.setGroupId(contactResult.getInt(1));
                Person person = getLightPerson(contactResult.getLong(2));
                c.setPerson(person);
                c.setUrl(contactResult.getString(3));
                c.setAlias(contactResult.getString(4));
                c.setLetterId(contactResult.getLong(5));
            }

            contactResult.close();

            return c;
        }

        contactResult.close();
        return c;
    }

    /**
     * Updates the contact with the person inside
     * 
     * @param contact
     * @return
     */
    public long updateFullContact(Contact contact) {

        String whereContact = KEY_ID + "=" + contact.getId();

        Person person = contact.getPerson();

        ContentValues contactValues = new ContentValues();
        contactValues.put(KEY_CONTACT_PERSON, updatePerson(person));
        contactValues.put(KEY_CONTACT_GROUP, contact.getGroupId());
        contactValues.put(KEY_CONTACT_URL, contact.getUrl());
        contactValues.put(KEY_CONTACT_ALIAS, contact.getAlias());
        contactValues.put(KEY_CONTACT_UPDATE, contact.getUpdated());
        contactValues.put(KEY_CONTACT_PHONEID, contact.getPhoneId());
        long oldIndex = contact.getLetterId();
        decrementLetterIndex(oldIndex);
        long newIndex = getLetterIndex(contact.getPerson().getName().substring(
                0, 1));
        incrementLetterIndex(newIndex);

        if (contact.getPhoneIndex() != 0)
            decrementPhoneIndex(contact.getPhoneIndex());
        if (contact.getMailIndex() != 0)
            decrementMailIndex(contact.getMailIndex());

        if (contact.getPerson().getTels().size() > 0) {
            long index = getPhoneIndex(contact.getPerson().getName().substring(
                    0, 1));
            incrementPhoneIndex(index);
        }
        if (contact.getPerson().getEmails().size() > 0) {
            long index = getMailIndex(contact.getPerson().getName().substring(
                    0, 1));
            incrementMailIndex(index);
        }

        db.update(DATABASE_TABLE_CONTACT, contactValues, whereContact, null);
        return contact.getId();

    }

    /**
     * Updates a full contact and updates the update timestamp of it
     * 
     * @param contact
     * @return
     */
    public long updateFullContactWithTimestamp(Contact contact) {
        contact.setUpdated(new Date().getTime());
        return updateFullContact(contact);
    }

    /**
     * Updates only the contact and not the person inside the contact.
     * 
     * @param contact
     * @return
     */
    public long updateContactOnly(Contact contact) {

        String whereContact = KEY_ID + "=" + contact.getId();

        ContentValues contactValues = new ContentValues();
        contactValues.put(KEY_CONTACT_GROUP, contact.getGroupId());
        contactValues.put(KEY_CONTACT_URL, contact.getUrl());
        contactValues.put(KEY_CONTACT_ALIAS, contact.getAlias());
        contactValues.put(KEY_CONTACT_UPDATE, contact.getUpdated());
        contactValues.put(KEY_CONTACT_PHONEID, contact.getPhoneId());
        db.update(DATABASE_TABLE_CONTACT, contactValues, whereContact, null);

        return contact.getId();

    }

    public long updatePerson(Person person) {

        long personId = person.getId();

        String wherePerson = KEY_ID + "=" + personId;

        db.delete(DATABASE_TABLE_ADDRESS, KEY_ADDRESS_PERSON + "=" + personId,
                null);
        db.delete(DATABASE_TABLE_TEL, KEY_TEL_PERSON + "=" + personId, null);
        db
                .delete(DATABASE_TABLE_EMAIL,
                        KEY_EMAIL_PERSON + "=" + personId, null);
        db.delete(DATABASE_TABLE_NETWORK, KEY_NETWORK_PERSON + "=" + personId,
                null);

        ContentValues personValues = new ContentValues();
        personValues.put(KEY_PERSON_NAME, person.getName());
        personValues.put(KEY_PERSON_FIRSTNAME, person.getFirstname());
        personValues.put(KEY_PERSON_NICKNAME, person.getNickname());
        personValues.put(KEY_PERSON_ABOUTME, person.getAboutMe());
        personValues.put(KEY_PERSON_COMPANY, person.getCompany());
        personValues.put(KEY_PERSON_TITLE, person.getTitle());
        personValues.put(KEY_PERSON_URL, person.getUrl());
        personValues.put(KEY_PERSON_BIRTHDAY, person.getBirthDay());
        personValues.put(KEY_PERSON_IMAGEURL, person.getPhoto().getImageUrl());

        db.update(DATABASE_TABLE_PERSON, personValues, wherePerson, null);

        List<Email> emails = person.getEmails();
        for (Email e : emails) {
            ContentValues emailValues = new ContentValues();
            emailValues.put(KEY_EMAIL_PERSON, personId);
            emailValues.put(KEY_EMAIL_TYPE, e.getTypeEmail());
            emailValues.put(KEY_EMAIL_VALUE, e.getEmail());
            db.insert(DATABASE_TABLE_EMAIL, null, emailValues);
        }

        List<Tel> tels = person.getTels();
        for (Tel t : tels) {
            ContentValues telValues = new ContentValues();
            telValues.put(KEY_TEL_PERSON, personId);
            telValues.put(KEY_TEL_TYPE, t.getType());
            telValues.put(KEY_TEL_VALUE, t.getPhoneNumber());
            db.insert(DATABASE_TABLE_TEL, null, telValues);
        }

        List<Address> adresses = person.getAddresses();
        for (Address a : adresses) {
            ContentValues adrValues = new ContentValues();
            adrValues.put(KEY_ADDRESS_PERSON, personId);
            adrValues.put(KEY_ADDRESS_TYPE, a.getType());
            adrValues.put(KEY_ADDRESS_VALUE, a.getFullValue());
            db.insert(DATABASE_TABLE_ADDRESS, null, adrValues);
        }

        List<NetworkItem> networks = person.getNetworkItems();
        for (NetworkItem n : networks) {
            ContentValues networkValues = new ContentValues();
            networkValues.put(KEY_NETWORK_PERSON, personId);
            networkValues.put(KEY_NETWORK_TYPE, n.getNetworkItemType());
            networkValues.put(KEY_NETWORK_VALUE, n.getNetworkItemLink());
            db.insert(DATABASE_TABLE_NETWORK, null, networkValues);
        }

        return personId;
    }

    /**
     * Return if the contact already exist in the database
     * 
     * @param url
     * @return
     */
    public boolean hasContact(String url) {
        Contact contact = getContactByURL(url);
        return contact != null;
    }

    /**
     * Get all contact in the database and all Person in the contact
     * 
     * @return
     */
    public List<Contact> getAllContacts() {
        SQLiteCursor cursor = (SQLiteCursor) db.query(DATABASE_TABLE_CONTACT,
                new String[] { KEY_ID, KEY_CONTACT_GROUP, KEY_CONTACT_PERSON,
                        KEY_CONTACT_URL, KEY_CONTACT_ALIAS, KEY_CONTACT_UPDATE,
                        KEY_CONTACT_PHONEID, KEY_CONTACT_LETTERID,
                        KEY_CONTACT_PHONEINDEX, KEY_CONTACT_MAILINDEX }, null,
                null, null, null, null);
        List<Contact> list = (List<Contact>) new ArrayList<Contact>();
        cursor.moveToFirst();
        for (int i = 0; i < cursor.getCount(); i++, cursor.moveToNext()) {
            Contact contact = new Contact();
            contact.setId(cursor.getLong(KEY_ID_COLUMN));
            contact.setAlias(cursor.getString(CONTACT_ALIAS_COLUMN));
            contact.setGroupId(cursor.getInt(CONTACT_GROUP_COLUMN));
            contact.setUrl(cursor.getString(CONTACT_URL_COLUMN));
            Person person = getPerson(cursor.getLong(CONTACT_PERSON_COLUMN));
            contact.setPerson(person);
            contact.setUpdated(cursor.getLong(CONTACT_UPDATE_COLUMN));
            contact.setPhoneId(cursor.getLong(CONTACT_PHONEID_COLUMN));
            contact.setLetterId(cursor.getLong(CONTACT_LETTERID_COLUMN));
            contact.setPhoneIndex(cursor.getLong(CONTACT_PHONEINDEX_COLUMN));
            contact.setMailIndex(cursor.getLong(CONTACT_MAILINDEX_COLUMN));
            list.add(contact);
        }
        cursor.close();
        return sortContacts(list);
    }

    /**
     * Light version of getAllContacts with minimum informations
     * 
     * @return
     */
    /*
     * public List<Contact> getAllLightContacts(boolean mail, boolean phone) {
     * long startTime = new Date().getTime(); SQLiteCursor cursor =
     * (SQLiteCursor) db.query(DATABASE_TABLE_CONTACT, new String[] { KEY_ID,
     * KEY_CONTACT_GROUP, KEY_CONTACT_PERSON, KEY_CONTACT_URL,
     * KEY_CONTACT_ALIAS, KEY_CONTACT_LETTERID }, null, null, null, null, null);
     * List<Contact> list = (List<Contact>) new ArrayList<Contact>();
     * cursor.moveToFirst();
     * 
     * if (mail) { for (int i = 0; i < cursor.getCount(); i++,
     * cursor.moveToNext()) { long personId = cursor.getLong(2); if
     * (personHasMail(personId)) { Person person = getLightPerson(personId); if
     * (person != null) { Contact contact = new Contact();
     * contact.setId(cursor.getLong(0)); contact.setGroupId(cursor.getInt(1));
     * contact.setPerson(person); contact.setUrl(cursor.getString(3));
     * contact.setAlias(cursor.getString(4));
     * contact.setLetterId(cursor.getLong(5)); list.add(contact); } } } } else
     * if (phone) { for (int i = 0; i < cursor.getCount(); i++,
     * cursor.moveToNext()) { long personId = cursor.getLong(2); if
     * (personHasPhone(personId)) { Person person = getLightPerson(personId); if
     * (person != null) { Contact contact = new Contact();
     * contact.setId(cursor.getLong(0)); contact.setGroupId(cursor.getInt(1));
     * contact.setPerson(person); contact.setUrl(cursor.getString(3));
     * contact.setAlias(cursor.getString(4));
     * contact.setLetterId(cursor.getLong(5)); list.add(contact); } } } } else {
     * for (int i = 0; i < cursor.getCount(); i++, cursor.moveToNext()) { long
     * personId = cursor.getLong(2);
     * 
     * Person person = getLightPerson(personId); if (person != null) { Contact
     * contact = new Contact(); contact.setId(cursor.getLong(0));
     * contact.setGroupId(cursor.getInt(1)); contact.setPerson(person);
     * contact.setUrl(cursor.getString(3));
     * contact.setAlias(cursor.getString(4));
     * contact.setLetterId(cursor.getLong(5)); list.add(contact); } } }
     * 
     * cursor.close(); long stopTime = new Date().getTime();
     * Log.w("DatabaseProvider getAllLightContacts execution time", "" +
     * (stopTime - startTime)); return sortContacts(list); }
     */

    /**
     * Alternative version for dev
     * 
     * @return
     */
    public List<Contact> getAllLightContacts(boolean mail, boolean phone) {
        long startTime = new Date().getTime();

        // select c._id,c.alias,p.name,p.firstname,p.imageurl from contact c,
        // person p, phone ph where c.person_id = p._id and ph.person_id=p._id;

        String sqlRequest = "SELECT DISTINCT (C." + KEY_ID + "),C."
                + KEY_CONTACT_ALIAS + ",P." + KEY_PERSON_NAME + ",P."
                + KEY_PERSON_FIRSTNAME + ",P." + KEY_PERSON_IMAGEURL + " FROM "
                + DATABASE_TABLE_CONTACT + " C, " + DATABASE_TABLE_PERSON
                + " P";
        if (phone)
            sqlRequest += ", " + DATABASE_TABLE_TEL + " PH";
        if (mail)
            sqlRequest += ", " + DATABASE_TABLE_EMAIL + " EM";
        sqlRequest += " WHERE C." + KEY_CONTACT_PERSON + " = P." + KEY_ID;
        if (phone)
            sqlRequest += " AND PH." + KEY_TEL_PERSON + " = P." + KEY_ID;
        if (mail)
            sqlRequest += " AND EM." + KEY_EMAIL_PERSON + " = P." + KEY_ID;

        Cursor cursor = db.rawQuery(sqlRequest, null);

        /*
         * SQLiteCursor cursor = (SQLiteCursor) db.query(DATABASE_TABLE_CONTACT,
         * new String[] { KEY_ID, KEY_CONTACT_GROUP, KEY_CONTACT_PERSON,
         * KEY_CONTACT_URL, KEY_CONTACT_ALIAS, KEY_CONTACT_LETTERID }, null,
         * null, null, null, null);
         */

        List<Contact> list = (List<Contact>) new ArrayList<Contact>();
        cursor.moveToFirst();

        for (int i = 0; i < cursor.getCount(); i++, cursor.moveToNext()) {
            Contact contact = new Contact();
            Person person = new Person();

            contact.setId(cursor.getLong(0));
            contact.setAlias(cursor.getString(1));
            person.setName(cursor.getString(2));
            person.setFirstname(cursor.getString(3));
            person.getPhoto().setImageUrl(cursor.getString(4));
            contact.setPerson(person);
            list.add(contact);

        }

        cursor.close();
        long stopTime = new Date().getTime();
        Log.w("DatabaseProvider ALTERNATEgetAllLightContacts execution time",
                "" + (stopTime - startTime));
        return sortContacts(list);
    }

    /**
     * Light version of getAllContacts with minimum informations
     * 
     * @return
     */
    public List<Contact> getAllLightContactsWithLimit(int start, int quantity) {
        SQLiteCursor cursor = (SQLiteCursor) db
                .query(DATABASE_TABLE_CONTACT, new String[] { KEY_ID,
                        KEY_CONTACT_GROUP, KEY_CONTACT_PERSON, KEY_CONTACT_URL,
                        KEY_CONTACT_ALIAS }, "1=1 LIMIT " + quantity
                        + " OFFSET " + start, null, null, null, null);
        List<Contact> list = (List<Contact>) new ArrayList<Contact>();
        cursor.moveToFirst();
        for (int i = 0; i < cursor.getCount(); i++, cursor.moveToNext()) {
            Contact contact = new Contact();
            contact.setId(cursor.getLong(0));
            contact.setGroupId(cursor.getInt(1));
            Person person = getLightPerson(cursor.getLong(2));
            contact.setPerson(person);
            contact.setUrl(cursor.getString(3));
            contact.setAlias(cursor.getString(4));
            list.add(contact);
        }
        cursor.close();
        return sortContacts(list);
    }

    /**
     * Light version of getAllContacts with minimum informations from a table of
     * ids
     * 
     * @return
     */
    public List<Contact> getAllLightContactsFromTable(long[] table) {
        long startTime = new Date().getTime();
        ArrayList<Contact> list = new ArrayList<Contact>();

        for (int i = 0; i < table.length; i++) {
            Contact c = getLightContact(table[i]);
            if (c != null)
                list.add(c);
        }

        long stopTime = new Date().getTime();
        Log.w("DatabaseProvider getAllLightContactsFromTable execution time",
                "" + (stopTime - startTime));
        return sortContacts(list);
    }

    public Contact addContact(Person person) {
        Contact c = new Contact();
        c.setPerson(person);
        c.setAlias(person.getFirstname() + " " + person.getName());
        long l = insertContact(c);
        c.setId(l);
        return c;
    }
    
    public Contact addContact(Contact contact) {
        long l = insertContact(contact);
        contact.setId(l);
        return contact;
    }

    public Contact addContact(Person person, String url) {
        Contact c = new Contact();
        c.setPerson(person);
        c.setUrl(url);
        c.setAlias(person.getFirstname() + " " + person.getName());

        long startTime = new Date().getTime();
        long old = getContactIdByURL(url);
        if (old != -1)
            removeContact(old);

        long stopTime = new Date().getTime();
        Log.e("CheckAndRemoveOld", stopTime - startTime + " ms");

        long l = insertContact(c);
        c.setId(l);
        return c;
    }

    private List<Contact> sortContacts(List<Contact> list) {
        Collections.sort(list, new Comparator<Contact>() {
            @Override
            public int compare(Contact object1, Contact object2) {
                return object1.getAlias().toLowerCase().compareTo(
                        object2.getAlias().toLowerCase());
            }
        });
        return list;
    }

    public long getPhoneIndex(String letter) {

        if (letter.length() != 1)
            return -1;

        String fullLetter = letter.toUpperCase();
        if (!alphabet.contains(fullLetter)) {
            fullLetter = "*";
        }

        String whereLetter = KEY_PHONEINDEX_LETTER + "= '" + fullLetter + "'";
        Cursor letterResult = db.query(DATABASE_TABLE_PHONEINDEX,
                new String[] { KEY_ID }, whereLetter, null, null, null, null);

        if (letterResult.getCount() > 0) {
            letterResult.moveToFirst();
            long index = letterResult.getLong(KEY_ID_COLUMN);
            letterResult.close();
            return index;
        } else {
            letterResult.close();
            return insertPhoneIndex(fullLetter);
        }
    }

    public long insertPhoneIndex(String letter) {

        ContentValues letterValues = new ContentValues();
        letterValues.put(KEY_PHONEINDEX_LETTER, letter);
        letterValues.put(KEY_PHONEINDEX_OCCU, 0);

        return db.insert(DATABASE_TABLE_PHONEINDEX, null, letterValues);
    }

    public long incrementPhoneIndex(long letterIndexId) {

        String whereLetter = KEY_ID + "=" + letterIndexId;

        Cursor letterResult = db.query(DATABASE_TABLE_PHONEINDEX, new String[] {
                KEY_ID, KEY_PHONEINDEX_OCCU }, whereLetter, null, null, null,
                null);

        int occu;
        if (letterResult.getCount() > 0) {
            letterResult.moveToFirst();
            occu = letterResult.getInt(1);
            letterResult.close();
            ContentValues letterValues = new ContentValues();
            letterValues.put(KEY_PHONEINDEX_OCCU, occu + 1);
            db.update(DATABASE_TABLE_PHONEINDEX, letterValues, whereLetter,
                    null);
            return occu + 1;
        } else
            return -1;
    }

    public long decrementPhoneIndex(long letterIndexId) {

        String whereLetter = KEY_ID + "=" + letterIndexId;

        Cursor letterResult = db.query(DATABASE_TABLE_PHONEINDEX, new String[] {
                KEY_ID, KEY_PHONEINDEX_OCCU }, whereLetter, null, null, null,
                null);

        int occu;
        if (letterResult.getCount() > 0) {
            letterResult.moveToFirst();
            occu = letterResult.getInt(1);
            letterResult.close();

            if (occu - 1 == 0) {
                removePhoneIndex(letterIndexId);
                return 0;
            } else {
                ContentValues letterValues = new ContentValues();
                letterValues.put(KEY_PHONEINDEX_OCCU, occu - 1);
                db.update(DATABASE_TABLE_PHONEINDEX, letterValues, whereLetter,
                        null);
                return occu - 1;
            }
        } else
            return -1;
    }

    public long removePhoneIndex(long letterIndexId) {

        String whereLetter = KEY_ID + "=" + letterIndexId;
        db.delete(DATABASE_TABLE_PHONEINDEX, whereLetter, null);
        return 0;
    }

    public List<LetterIndex> getAllPhoneIndexes() {
        List<LetterIndex> letterIndexes = new ArrayList<LetterIndex>();
        Cursor allIndexes = db.query(DATABASE_TABLE_PHONEINDEX, new String[] {
                KEY_ID, KEY_PHONEINDEX_LETTER, KEY_PHONEINDEX_OCCU }, null,
                null, null, null, null);

        if (allIndexes.moveToFirst()) {
            do {
                LetterIndex i = new LetterIndex(allIndexes
                        .getString(PHONEINDEX_LETTER_COLUMN), allIndexes
                        .getInt(PHONEINDEX_OCCU_COLUMN));
                i.setId(allIndexes.getLong(KEY_ID_COLUMN));
                i.setType(LetterIndex.TYPE_PHONE);
                letterIndexes.add(i);
            } while (allIndexes.moveToNext());
        }

        allIndexes.close();

        Collections.sort(letterIndexes, new Comparator<LetterIndex>() {
            @Override
            public int compare(LetterIndex object1, LetterIndex object2) {
                return -object1.getLetter().toUpperCase().compareTo(
                        object2.getLetter().toUpperCase());
            }
        });
        return letterIndexes;
    }

    /**
     * Gets all the light contacts in a specific letterindex
     * 
     * @param letterIndexId
     *            The Letter Index to get all the contacts for
     * @return An ArrayList of contacts
     */
    public List<Contact> getAllLightContactsFromPhoneIndex(long letterIndexId) {
        List<Contact> groupContacts = new ArrayList<Contact>();

        String whereContacts = KEY_CONTACT_PHONEINDEX + "=" + letterIndexId;
        Cursor validContacts = db.query(DATABASE_TABLE_CONTACT, new String[] {
                KEY_ID, KEY_CONTACT_GROUP, KEY_CONTACT_PERSON, KEY_CONTACT_URL,
                KEY_CONTACT_ALIAS, KEY_CONTACT_PHONEINDEX }, whereContacts,
                null, null, null, null);

        if (validContacts.moveToFirst()) {

            do {
                Contact contact = new Contact();
                contact.setId(validContacts.getLong(0));
                contact.setGroupId(validContacts.getInt(1));
                Person person = getLightPerson(validContacts.getLong(2));
                contact.setPerson(person);
                contact.setUrl(validContacts.getString(3));
                contact.setAlias(validContacts.getString(4));
                contact.setPhoneIndex(validContacts.getLong(5));
                groupContacts.add(contact);
            } while (validContacts.moveToNext());

        }
        validContacts.close();

        return groupContacts;
    }

    public long getMailIndex(String letter) {

        if (letter.length() != 1)
            return -1;

        String fullLetter = letter.toUpperCase();
        if (!alphabet.contains(fullLetter)) {
            fullLetter = "*";
        }

        String whereLetter = KEY_MAILINDEX_LETTER + "= '" + fullLetter + "'";
        Cursor letterResult = db.query(DATABASE_TABLE_MAILINDEX,
                new String[] { KEY_ID }, whereLetter, null, null, null, null);

        if (letterResult.getCount() > 0) {
            letterResult.moveToFirst();
            long index = letterResult.getLong(KEY_ID_COLUMN);
            letterResult.close();
            return index;
        } else {
            letterResult.close();
            return insertMailIndex(fullLetter);
        }
    }

    public long insertMailIndex(String letter) {

        ContentValues letterValues = new ContentValues();
        letterValues.put(KEY_MAILINDEX_LETTER, letter);
        letterValues.put(KEY_MAILINDEX_OCCU, 0);

        return db.insert(DATABASE_TABLE_MAILINDEX, null, letterValues);
    }

    public long incrementMailIndex(long letterIndexId) {

        String whereLetter = KEY_ID + "=" + letterIndexId;

        Cursor letterResult = db.query(DATABASE_TABLE_MAILINDEX, new String[] {
                KEY_ID, KEY_MAILINDEX_OCCU }, whereLetter, null, null, null,
                null);

        int occu;
        if (letterResult.getCount() > 0) {
            letterResult.moveToFirst();
            occu = letterResult.getInt(1);
            letterResult.close();
            ContentValues letterValues = new ContentValues();
            letterValues.put(KEY_MAILINDEX_OCCU, occu + 1);
            db
                    .update(DATABASE_TABLE_MAILINDEX, letterValues,
                            whereLetter, null);
            return occu + 1;
        } else
            return -1;
    }

    public long decrementMailIndex(long letterIndexId) {

        String whereLetter = KEY_ID + "=" + letterIndexId;

        Cursor letterResult = db.query(DATABASE_TABLE_MAILINDEX, new String[] {
                KEY_ID, KEY_MAILINDEX_OCCU }, whereLetter, null, null, null,
                null);

        int occu;
        if (letterResult.getCount() > 0) {
            letterResult.moveToFirst();
            occu = letterResult.getInt(1);
            letterResult.close();

            if (occu - 1 == 0) {
                removeMailIndex(letterIndexId);
                return 0;
            } else {
                ContentValues letterValues = new ContentValues();
                letterValues.put(KEY_MAILINDEX_OCCU, occu - 1);
                db.update(DATABASE_TABLE_MAILINDEX, letterValues, whereLetter,
                        null);
                return occu - 1;
            }
        } else
            return -1;
    }

    public long removeMailIndex(long letterIndexId) {

        String whereLetter = KEY_ID + "=" + letterIndexId;
        db.delete(DATABASE_TABLE_MAILINDEX, whereLetter, null);
        return 0;
    }

    public List<LetterIndex> getAllMailIndexes() {
        List<LetterIndex> letterIndexes = new ArrayList<LetterIndex>();
        Cursor allIndexes = db.query(DATABASE_TABLE_MAILINDEX, new String[] {
                KEY_ID, KEY_MAILINDEX_LETTER, KEY_MAILINDEX_OCCU }, null, null,
                null, null, null);

        if (allIndexes.moveToFirst()) {
            do {
                LetterIndex i = new LetterIndex(allIndexes
                        .getString(MAILINDEX_LETTER_COLUMN), allIndexes
                        .getInt(MAILINDEX_OCCU_COLUMN));
                i.setId(allIndexes.getLong(KEY_ID_COLUMN));
                i.setType(LetterIndex.TYPE_MAIL);
                letterIndexes.add(i);
            } while (allIndexes.moveToNext());
        }

        allIndexes.close();

        Collections.sort(letterIndexes, new Comparator<LetterIndex>() {
            @Override
            public int compare(LetterIndex object1, LetterIndex object2) {
                return -object1.getLetter().toUpperCase().compareTo(
                        object2.getLetter().toUpperCase());
            }
        });
        return letterIndexes;
    }

    /**
     * Gets all the light contacts in a specific letterindex
     * 
     * @param letterIndexId
     *            The Letter Index to get all the contacts for
     * @return An ArrayList of contacts
     */
    public List<Contact> getAllLightContactsFromMailIndex(long letterIndexId) {
        List<Contact> groupContacts = new ArrayList<Contact>();

        String whereContacts = KEY_CONTACT_MAILINDEX + "=" + letterIndexId;
        Cursor validContacts = db.query(DATABASE_TABLE_CONTACT, new String[] {
                KEY_ID, KEY_CONTACT_GROUP, KEY_CONTACT_PERSON, KEY_CONTACT_URL,
                KEY_CONTACT_ALIAS, KEY_CONTACT_MAILINDEX }, whereContacts,
                null, null, null, null);

        if (validContacts.moveToFirst()) {

            do {
                Contact contact = new Contact();
                contact.setId(validContacts.getLong(0));
                contact.setGroupId(validContacts.getInt(1));
                Person person = getLightPerson(validContacts.getLong(2));
                contact.setPerson(person);
                contact.setUrl(validContacts.getString(3));
                contact.setAlias(validContacts.getString(4));
                contact.setMailIndex(validContacts.getLong(5));
                groupContacts.add(contact);
            } while (validContacts.moveToNext());

        }
        validContacts.close();

        return groupContacts;
    }

    public long getLetterIndex(String letter) {

        if (letter.length() != 1)
            return -1;

        String fullLetter = letter.toUpperCase();
        if (!alphabet.contains(fullLetter)) {
            fullLetter = "*";
        }

        String whereLetter = KEY_LETTERINDEX_LETTER + "= '" + fullLetter + "'";
        Cursor letterResult = db.query(DATABASE_TABLE_LETTERINDEX,
                new String[] { KEY_ID }, whereLetter, null, null, null, null);

        if (letterResult.getCount() > 0) {
            letterResult.moveToFirst();
            long index = letterResult.getLong(KEY_ID_COLUMN);
            letterResult.close();
            return index;
        } else {
            letterResult.close();
            return insertLetterIndex(fullLetter);
        }
    }

    public long insertLetterIndex(String letter) {

        ContentValues letterValues = new ContentValues();
        letterValues.put(KEY_LETTERINDEX_LETTER, letter);
        letterValues.put(KEY_LETTERINDEX_OCCU, 0);

        return db.insert(DATABASE_TABLE_LETTERINDEX, null, letterValues);
    }

    public long incrementLetterIndex(long letterIndexId) {

        String whereLetter = KEY_ID + "=" + letterIndexId;

        Cursor letterResult = db.query(DATABASE_TABLE_LETTERINDEX,
                new String[] { KEY_ID, KEY_LETTERINDEX_OCCU }, whereLetter,
                null, null, null, null);

        int occu;
        if (letterResult.getCount() > 0) {
            letterResult.moveToFirst();
            occu = letterResult.getInt(1);
            letterResult.close();
            ContentValues letterValues = new ContentValues();
            letterValues.put(KEY_LETTERINDEX_OCCU, occu + 1);
            db.update(DATABASE_TABLE_LETTERINDEX, letterValues, whereLetter,
                    null);
            return occu + 1;
        } else
            return -1;
    }

    public long decrementLetterIndex(long letterIndexId) {

        String whereLetter = KEY_ID + "=" + letterIndexId;

        Cursor letterResult = db.query(DATABASE_TABLE_LETTERINDEX,
                new String[] { KEY_ID, KEY_LETTERINDEX_OCCU }, whereLetter,
                null, null, null, null);

        int occu;
        if (letterResult.getCount() > 0) {
            letterResult.moveToFirst();
            occu = letterResult.getInt(1);
            letterResult.close();

            if (occu - 1 == 0) {
                removeLetterIndex(letterIndexId);
                return 0;
            } else {
                ContentValues letterValues = new ContentValues();
                letterValues.put(KEY_LETTERINDEX_OCCU, occu - 1);
                db.update(DATABASE_TABLE_LETTERINDEX, letterValues,
                        whereLetter, null);
                return occu - 1;
            }
        } else
            return -1;
    }

    public long removeLetterIndex(long letterIndexId) {

        String whereLetter = KEY_ID + "=" + letterIndexId;
        db.delete(DATABASE_TABLE_LETTERINDEX, whereLetter, null);
        return 0;
    }

    public List<LetterIndex> getAllLetterIndexes() {
        List<LetterIndex> letterIndexes = new ArrayList<LetterIndex>();
        Cursor allIndexes = db.query(DATABASE_TABLE_LETTERINDEX, new String[] {
                KEY_ID, KEY_LETTERINDEX_LETTER, KEY_LETTERINDEX_OCCU }, null,
                null, null, null, null);

        if (allIndexes.moveToFirst()) {
            do {
                LetterIndex i = new LetterIndex(allIndexes
                        .getString(LETTERINDEX_LETTER_COLUMN), allIndexes
                        .getInt(LETTERINDEX_OCCU_COLUMN));
                i.setId(allIndexes.getLong(KEY_ID_COLUMN));
                i.setType(LetterIndex.TYPE_ROOT);
                letterIndexes.add(i);
            } while (allIndexes.moveToNext());
        }

        allIndexes.close();

        Collections.sort(letterIndexes, new Comparator<LetterIndex>() {
            @Override
            public int compare(LetterIndex object1, LetterIndex object2) {
                return -object1.getLetter().toUpperCase().compareTo(
                        object2.getLetter().toUpperCase());
            }
        });
        return letterIndexes;
    }

    /**
     * Gets all the light contacts in a specific letterindex
     * 
     * @param letterIndexId
     *            The Letter Index to get all the contacts for
     * @return An ArrayList of contacts
     */
    public List<Contact> getAllLightContactsFromLetterIndex(long letterIndexId) {
        List<Contact> groupContacts = new ArrayList<Contact>();

        String whereContacts = KEY_CONTACT_LETTERID + "=" + letterIndexId;
        Cursor validContacts = db.query(DATABASE_TABLE_CONTACT, new String[] {
                KEY_ID, KEY_CONTACT_GROUP, KEY_CONTACT_PERSON, KEY_CONTACT_URL,
                KEY_CONTACT_ALIAS, KEY_CONTACT_LETTERID }, whereContacts, null,
                null, null, null);

        if (validContacts.moveToFirst()) {

            do {
                Contact contact = new Contact();
                contact.setId(validContacts.getLong(0));
                contact.setGroupId(validContacts.getInt(1));
                Person person = getLightPerson(validContacts.getLong(2));
                contact.setPerson(person);
                contact.setUrl(validContacts.getString(3));
                contact.setAlias(validContacts.getString(4));
                contact.setLetterId(validContacts.getLong(5));
                groupContacts.add(contact);
            } while (validContacts.moveToNext());

        }
        validContacts.close();

        return groupContacts;
    }

    public boolean personHasPhone(long personId) {
        long startTime = new Date().getTime();
        String whereTel = KEY_TEL_PERSON + " = " + personId;
        SQLiteStatement r = db.compileStatement("SELECT count(*) FROM "
                + DATABASE_TABLE_TEL + " WHERE " + whereTel);
        long result = r.simpleQueryForLong();
        long stopTime = new Date().getTime();
        Log.w("HiveActivity.onCreate execution time", ""
                + (stopTime - startTime));
        if (result > 0)
            return true;
        else
            return false;
    }

    public boolean personHasMail(long personId) {
        long startTime = new Date().getTime();
        String whereEmail = KEY_EMAIL_PERSON + " = " + personId;

        SQLiteStatement r = db.compileStatement("SELECT count(*) FROM "
                + DATABASE_TABLE_EMAIL + " WHERE " + whereEmail);
        long result = r.simpleQueryForLong();
        long stopTime = new Date().getTime();
        Log.w("HiveActivity.onCreate execution time", ""
                + (stopTime - startTime));
        if (result > 0)
            return true;
        else
            return false;
    }
}
